﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class ItemManager:ConnectionManager
    {
        #region Item Master data

        #region General

        public bool IsForQuarantine(string _ITEMCODE)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [ItemCode] = @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", _ITEMCODE));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            bool _isInventory = (bool)ds.Tables[0].Rows[0]["IsForQuarantine"];
            return _isInventory;
        }

        public bool IsInventoryItem(string _ITEMCODE)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [ItemCode] = @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", _ITEMCODE));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            bool _isInventory =  (bool)ds.Tables[0].Rows[0]["IsInventoryItem"];
            return _isInventory;
        }

        public bool IsSalesItem(string _ITEMCODE)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [ItemCode] = @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", _ITEMCODE));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            bool _isSalesItem = (bool)ds.Tables[0].Rows[0]["IsSalesItem"];

            return _isSalesItem;
        }

        public DataTable BrowseItemMasterDataByName(string sItemName)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [DESCRIPTION] LIKE @DESCRIPTION";
            oCommand.Parameters.Add(new SqlParameter("@DESCRIPTION", sItemName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseItemMasterDataByCode(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [ITEMCODE] LIKE @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseItemMasterDataByName(string sItemName, string sItemGroup)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [DESCRIPTION] LIKE @DESCRIPTION AND ItemGroup=@ItemGroup";
            oCommand.Parameters.Add(new SqlParameter("@DESCRIPTION", sItemName));
            oCommand.Parameters.Add(new SqlParameter("@ItemGroup", sItemGroup));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseItemMasterDataByCode(string sItemCode, string sItemGroup)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [ITEMCODE] LIKE @ITEMCODE AND ItemGroup=@ItemGroup";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemGroup", sItemGroup));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseInventoryItemByName(string sItemName, string sItemGroup)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemName = '%' + sItemName + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [DESCRIPTION] LIKE @DESCRIPTION AND ItemGroup=@ItemGroup AND IsInventoryItem=1";
            oCommand.Parameters.Add(new SqlParameter("@DESCRIPTION", sItemName));
            oCommand.Parameters.Add(new SqlParameter("@ItemGroup", sItemGroup));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseInventoryItemByCode(string sItemCode, string sItemGroup)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemCode = '%' + sItemCode + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [ITEMCODE] LIKE @ITEMCODE AND ItemGroup=@ItemGroup  AND IsInventoryItem=1";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemGroup", sItemGroup));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseProductionItemByName(string sItemName, string sItemGroup)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemName = '%' + sItemName + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [DESCRIPTION] LIKE @DESCRIPTION AND ItemGroup=@ItemGroup AND IsInventoryItem=1 AND IsProduction = 1";
            oCommand.Parameters.Add(new SqlParameter("@DESCRIPTION", sItemName));
            oCommand.Parameters.Add(new SqlParameter("@ItemGroup", sItemGroup));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseProductionItemByCode(string sItemCode, string sItemGroup)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemCode = '%' + sItemCode + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [ITEMCODE] LIKE @ITEMCODE AND ItemGroup=@ItemGroup  AND IsInventoryItem=1 AND IsProduction = 1";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemGroup", sItemGroup));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetItemMasterData(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE [ITEMCODE] = @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetItemMasterList(string _ITEM_GROUP)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_ITEMMASTER WHERE ITEMGROUP=@ITEMGROUP";
            oCommand.Parameters.Add(new SqlParameter("@ITEMGROUP", _ITEM_GROUP));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseItemMasterData()
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 100 * FROM VW_ITEMMASTER";


            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        #endregion

        #region Inventory Item
        
        public DataTable GetInventoryItemByCode(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemCode = '%' + sItemCode.Trim() + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT ItemCode, [Description] ItemName, IntyUntMsr UoM FROM ITEMMASTER WHERE [ITEMCODE] LIKE @ITEMCODE AND IsInventoryItem = 1";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInventoryItemByName(string sItemName)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemName = '%' + sItemName.Trim() + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT  ItemCode, [Description] ItemName, IntyUntMsr UoM FROM ITEMMASTER WHERE [ITEMNAME] LIKE @ITEMNAME AND IsInventoryItem = 1";
            oCommand.Parameters.Add(new SqlParameter("@ITEMNAME", sItemName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion

        #region Purchased Item
        public DataTable GetPurchasedItem()
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE ISPURCHASEDITEM = 1 AND ITEMMASTER.ISACTIVE = 1";

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowsePurItmsByCode(string sItemGroup, string sItemCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                sItemCode = string.Format("{0}{1}{2}", "%", sItemCode.Trim(), "%");
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_PURCHASEDITEM WHERE [ITEMCODE] LIKE @ITEMCODE AND ItemGroup=@ItemGroup";
                oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));
                oCommand.Parameters.Add(new SqlParameter("@ItemGroup", sItemGroup));
                

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }

        public DataTable BrowsePurItmsByName(string sItemGroup, string sItemCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                sItemCode = string.Format("{0}{1}{2}", "%", sItemCode.Trim(), "%");
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_PURCHASEDITEM WHERE [ITEMNAME] LIKE @ITEMNAME AND ITEMGROUP=@ITEMGROUP";
                oCommand.Parameters.Add(new SqlParameter("@ITEMNAME", sItemCode));
                oCommand.Parameters.Add(new SqlParameter("@ITEMGROUP", sItemGroup));
                

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetPurItmsByCode(string sItemCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                //sItemCode = string.Format("{0}{1}{2}", "%", sItemCode.Trim(), "%");
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_PURCHASEDITEM WHERE [ITEMCODE] = @ITEMCODE";
                oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));


                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region Sales Item

        public DataTable BrowseNonInventorySalesItemByCode(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemCode = '%' + sItemCode + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE IsInventoryItem=0 AND IsSalesItem=1 AND [ITEMCODE] LIKE @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseNonInventorySalesItemByName(string sItemName)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemName = '%' + sItemName + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE IsInventoryItem=0 AND IsSalesItem=1 AND [DESCRIPTION] LIKE @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseInventorySalesItemByCode(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemCode = '%' + sItemCode + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE IsInventoryItem=1 AND IsSalesItem=1 AND [ITEMCODE] LIKE @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseInventorySalesItemByName(string sItemName)
        {
            SqlCommand oCommand = new SqlCommand();
            sItemName = '%' + sItemName + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE IsInventoryItem=1 AND IsSalesItem=1 AND [DESCRIPTION] LIKE @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseSalesItemByCode(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE IsSalesItem=1 AND [ITEMCODE] LIKE @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseSalesItemByName(string sItemName)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE IsSalesItem=1 AND [ITEMNAME] LIKE @ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowseSlsItemByCodeWithPrice(string sPriceListCode, string sItemCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                sItemCode = string.Format("{0}{1}", sItemCode.Trim(), "%");
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_ITEM_W_PRICE WHERE PriceListCode=@PRICELISTCODE AND [ITEMCODE] LIKE @ITEMCODE";
                oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));
                oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", sPriceListCode));

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }

        public DataTable BrowseSlsItemByNameWithPrice(string sPriceListCode, string sItemCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                sItemCode = string.Format("{0}{1}",  sItemCode.Trim(), "%");
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_ITEM_W_PRICE WHERE PriceListCode=@PRICELISTCODE AND [DESCRIPTION] LIKE @ITEMCODE";
                oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));
                oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", sPriceListCode));

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }


        public DataTable GetSlsItemByCodeWithPrice(string sPriceListCode, string sItemCode)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_ITEM_W_PRICE WHERE PriceListCode=@PRICELISTCODE AND [ITEMCODE] = @ITEMCODE";
                oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));
                oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", sPriceListCode));

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }


        public DataTable GetSlsItemByNameWithPrice(string sPriceListCode, string _DESCRIPTION)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_ITEM_W_PRICE WHERE PriceListCode=@PRICELISTCODE AND [Description]  = @Description";
                oCommand.Parameters.Add(new SqlParameter("@Description", _DESCRIPTION));
                oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", sPriceListCode));

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetSalesItem()
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                //sItemCode = string.Format("{0}{1}{2}", "%", sItemCode.Trim(), "%");
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE IsSalesItem=1 and IsActive=1";
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }


        public DataTable GetSalesItemByItemCode(string _ITEMCODE)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                //sItemCode = string.Format("{0}{1}{2}", "%", sItemCode.Trim(), "%");
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ITEMMASTER WHERE IsSalesItem=1 and IsActive=1 AND ItemCode=@ItemCode";
                oCommand.Parameters.Add(new SqlParameter("ItemCode", _ITEMCODE));
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }

        #endregion

        #region BOM

        public string GetDefaultBOM(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT BOMCode FROM ITEMMASTER WHERE ITEMCODE=@ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0].Rows[0]["BOMCode"].ToString();
        }

        #endregion

        #endregion

        #region Item Division/Category/Classification
        public DataTable GetItemDivision()
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DivisionCode OldDivCode, * FROM ITEM_DIVISION";
            
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        
        public DataTable GetItemDivision(string sGroup)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ITEM_DIVISION WHERE ITEMGROUP=@ITEMGROUP";
            oCommand.Parameters.Add(new SqlParameter("@ITEMGROUP", sGroup));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetItemCategory()
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DivisionCode OldCatCode, * FROM ITEM_CATEGORY";

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetItemCategory(string sDivision)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT CategoryCode OldCatCode, * FROM ITEM_CATEGORY WHERE DIVCODE=@DIVISION ORDER BY CATEGORYCODE";
            oCommand.Parameters.Add(new SqlParameter("@DIVISION", sDivision));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetClassification(string sDivision, string sCategory)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT ClssCode OldClssCode, * FROM ITEM_CLASS WHERE DIVCODE=@DIVISION and CategoryCode=@CategoryCode";
            oCommand.Parameters.Add(new SqlParameter("@DIVISION", sDivision));
            oCommand.Parameters.Add(new SqlParameter("@CategoryCode", sCategory));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        #endregion

        #region Item Add/Update

        public SqlException AddItem(ItemUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandType = CommandType.Text;
                oCommand.CommandText = "INSERT INTO ItemMaster (ItemCode, Description, BarCode, UnitPrice, ItemType, ItemGroup, ItemClass, ItemCategory, IsProduction, IsForQuarantine, IsPurchasedItem, IsSalesItem, IsInventoryItem, IsFixedAsset, IsActive, Manufctr, ItmIdent, MfrCatalog, PurUntMsr, ItmPerPurUnit, SalUntMsr, ItmPerSaleUnit, InvtyUntMsr, SmallestInvtyUoM, ItemDivision, Brand, ProductCategory, Product, Variant, ProductSize, ProductUnit, MinStock, MaxStock, BOMCode, AcctCode, AcctName) VALUES (@ItemCode, @Description, @BarCode, @UnitPrice, @ItemType, @ItemGroup, @ItemClass, @ItemCategory, @IsProduction, @IsForQuarantine,@IsPurchasedItem, @IsSalesItem, @IsInventoryItem, @IsFixedAsset, @IsActive, @Manufctr, @ItmIdent, @MfrCatalog, @PurUntMsr, @ItmPerPurUnit, @SalUntMsr, @ItmPerSaleUnit, @InvtyUntMsr, @SmallestInvtyUoM, @ItemDivision, @Brand, @ProductCategory, @Product, @Variant, @ProductSize, @ProductUnit, @MinStock, @MaxStock, @BOMCode, @AcctCode, @AcctName)";

                
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@Description", oUnit.Description));
                oCommand.Parameters.Add(new SqlParameter("@BarCode", oUnit.BarCode));
                oCommand.Parameters.Add(new SqlParameter("@UnitPrice", oUnit.UnitPrice));
                oCommand.Parameters.Add(new SqlParameter("@ItemType", oUnit.ItemType));
                oCommand.Parameters.Add(new SqlParameter("@ItemGroup", oUnit.ItemGroup));
                oCommand.Parameters.Add(new SqlParameter("@ItemDivision", oUnit.ItemDivision));
                oCommand.Parameters.Add(new SqlParameter("@ItemCategory", oUnit.ItemCategory));
                oCommand.Parameters.Add(new SqlParameter("@ItemClass", oUnit.ItemClass));

                oCommand.Parameters.Add(new SqlParameter("@AcctCode", oUnit.AccountCode));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AccountName));
                oCommand.Parameters.Add(new SqlParameter("@IsProduction", oUnit.IsProduction));
                oCommand.Parameters.Add(new SqlParameter("@IsForQuarantine", oUnit.IsForQuarantine));
                oCommand.Parameters.Add(new SqlParameter("@IsPurchasedItem", oUnit.IsPurchasedItem));
                oCommand.Parameters.Add(new SqlParameter("@IsSalesItem", oUnit.IsSalesItem));
                oCommand.Parameters.Add(new SqlParameter("@IsInventoryItem", oUnit.IsInventoryItem));
                oCommand.Parameters.Add(new SqlParameter("@IsFixedAsset", oUnit.IsFixedAsset));
                //oCommand.Parameters.Add(new SqlParameter("@DaysBefExpire", oUnit.NoDaysBefExpiration));
                oCommand.Parameters.Add(new SqlParameter("@IsActive", oUnit.IsActive));
                oCommand.Parameters.Add(new SqlParameter("@Manufctr", oUnit.Manufacturer));
                oCommand.Parameters.Add(new SqlParameter("@ItmIdent", oUnit.AdditionalIdentifier));
                oCommand.Parameters.Add(new SqlParameter("@MfrCatalog", oUnit.ManufacturingCatalogNo));
                oCommand.Parameters.Add(new SqlParameter("@PurUntMsr", oUnit.PurchasingUOM));
                oCommand.Parameters.Add(new SqlParameter("@ItmPerPurUnit", oUnit.ItemPerPurchaseUnit));
                oCommand.Parameters.Add(new SqlParameter("@SalUntMsr", oUnit.SalesUOM));
                oCommand.Parameters.Add(new SqlParameter("@ItmPerSaleUnit", oUnit.ItemPerSalesUnit));
                oCommand.Parameters.Add(new SqlParameter("@InvtyUntMsr", oUnit.InvntyUOM));
                oCommand.Parameters.Add(new SqlParameter("@SmallestInvtyUoM", oUnit.SmallestInvntyUOM));
                //@Brand, @ProductCategory, @Product, @Variant, @ProductSize, @ProductUnit

                oCommand.Parameters.Add(new SqlParameter("@Brand", oUnit.Brand));
                oCommand.Parameters.Add(new SqlParameter("@ProductCategory", oUnit.ProductCategory));
                oCommand.Parameters.Add(new SqlParameter("@Product", oUnit.Product));
                oCommand.Parameters.Add(new SqlParameter("@Variant", oUnit.Variant));
                oCommand.Parameters.Add(new SqlParameter("@ProductSize", oUnit.Size));
                oCommand.Parameters.Add(new SqlParameter("@ProductUnit", oUnit.ProductUnit));
                oCommand.Parameters.Add(new SqlParameter("@MinStock", oUnit.MinStock));
                oCommand.Parameters.Add(new SqlParameter("@MaxStock", oUnit.MaxStock));
                oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateItem(ItemUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {

                oCommand.Connection = this.Connection;

                oCommand.CommandType = CommandType.Text;
                oCommand.CommandText = "UPDATE ItemMaster SET Description=@Description, BarCode=@BarCode, UnitPrice=@UnitPrice, ItemType=@ItemType, " +
                                            "ItemGroup=@ItemGroup, ItemClass=@ItemClass, ItemCategory=@ItemCategory,IsProduction=@IsProduction, IsForQuarantine=@IsForQuarantine, IsPurchasedItem=@IsPurchasedItem, IsSalesItem=@IsSalesItem, IsInventoryItem=@IsInventoryItem, IsFixedAsset=@IsFixedAsset, IsActive=@IsActive, Manufctr=@Manufctr, ItmIdent=@ItmIdent, MfrCatalog=@MfrCatalog, PurUntMsr=@PurUntMsr, ItmPerPurUnit=@ItmPerPurUnit, SalUntMsr=@SalUntMsr, ItmPerSaleUnit=@ItmPerSaleUnit, InvtyUntMsr=@InvtyUntMsr, SmallestInvtyUoM=@SmallestInvtyUoM, ItemDivision=@ItemDivision," +
                                            " Brand=@Brand, ProductCategory=@ProductCategory, Product=@Product, Variant=@Variant, ProductSize=@ProductSize, ProductUnit=@ProductUnit, MaxStock=@MaxStock, MinStock=@MinStock, BOMCode=@BOMCode, AcctCode=@AcctCode, AcctName=@AcctName" +
                                            " WHERE ItemCode=@ItemCode";

                oCommand.Parameters.Add(new SqlParameter("@SmallestInvtyUoM", oUnit.SmallestInvntyUOM));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ItemCode));
                oCommand.Parameters.Add(new SqlParameter("@Description", oUnit.Description));
                oCommand.Parameters.Add(new SqlParameter("@BarCode", oUnit.BarCode));
                oCommand.Parameters.Add(new SqlParameter("@UnitPrice", oUnit.UnitPrice));
                oCommand.Parameters.Add(new SqlParameter("@ItemType", oUnit.ItemType));
                oCommand.Parameters.Add(new SqlParameter("@ItemGroup", oUnit.ItemGroup));
                oCommand.Parameters.Add(new SqlParameter("@ItemDivision", oUnit.ItemDivision));
                oCommand.Parameters.Add(new SqlParameter("@ItemClass", oUnit.ItemClass));
                oCommand.Parameters.Add(new SqlParameter("@ItemCategory", oUnit.ItemCategory));
                oCommand.Parameters.Add(new SqlParameter("@IsProduction", oUnit.IsProduction));
                oCommand.Parameters.Add(new SqlParameter("@IsForQuarantine", oUnit.IsForQuarantine));
                oCommand.Parameters.Add(new SqlParameter("@IsPurchasedItem", oUnit.IsPurchasedItem));
                oCommand.Parameters.Add(new SqlParameter("@IsSalesItem", oUnit.IsSalesItem));
                oCommand.Parameters.Add(new SqlParameter("@IsInventoryItem", oUnit.IsInventoryItem));
                oCommand.Parameters.Add(new SqlParameter("@IsFixedAsset", oUnit.IsFixedAsset));
                oCommand.Parameters.Add(new SqlParameter("@IsActive", oUnit.IsActive));
                oCommand.Parameters.Add(new SqlParameter("@MinStock", oUnit.MinStock));
                oCommand.Parameters.Add(new SqlParameter("@MaxStock", oUnit.MaxStock));

                oCommand.Parameters.Add(new SqlParameter("@AcctCode", oUnit.AccountCode));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AccountName));

                //Manufctr=@Manufctr, ItmIdent=@ItmIdent, MfrCatalog=@MfrCatalog, PurUntMsr=@PurUntMsr, 
                //ItmPerPurUnit=@ItmPerPurUnit, SalUntMsr=@SalUntMsr, ItmPerSaleUnit=@ItmPerSaleUnit, InvtyUntMsr=@                

                oCommand.Parameters.Add(new SqlParameter("@Manufctr", oUnit.Manufacturer));
                oCommand.Parameters.Add(new SqlParameter("@ItmIdent", oUnit.AdditionalIdentifier));
                oCommand.Parameters.Add(new SqlParameter("@MfrCatalog", oUnit.ManufacturingCatalogNo));
                oCommand.Parameters.Add(new SqlParameter("@PurUntMsr", oUnit.PurchasingUOM));
                oCommand.Parameters.Add(new SqlParameter("@ItmPerPurUnit", oUnit.ItemPerPurchaseUnit));
                oCommand.Parameters.Add(new SqlParameter("@SalUntMsr", oUnit.SalesUOM));
                oCommand.Parameters.Add(new SqlParameter("@ItmPerSaleUnit", oUnit.ItemPerSalesUnit));
                oCommand.Parameters.Add(new SqlParameter("@InvtyUntMsr", oUnit.InvntyUOM));

                oCommand.Parameters.Add(new SqlParameter("@Brand", oUnit.Brand));
                oCommand.Parameters.Add(new SqlParameter("@ProductCategory", oUnit.ProductCategory));
                oCommand.Parameters.Add(new SqlParameter("@Product", oUnit.Product));
                oCommand.Parameters.Add(new SqlParameter("@Variant", oUnit.Variant));
                oCommand.Parameters.Add(new SqlParameter("@ProductSize", oUnit.Size));
                oCommand.Parameters.Add(new SqlParameter("@ProductUnit", oUnit.ProductUnit));
                oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));


                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateItemDivision(DataTable dt)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                foreach (DataRow oRow in dt.Rows)
                {
                    if (oRow.RowState == DataRowState.Added)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO ITEM_DIVISION (DivisionCode, DivisionName, ITEMGROUP) VALUES (@DivisionCode, @DivisionName, @ITEMGROUP)";
                        oCommand.Parameters.Add(new SqlParameter("@DivisionCode", oRow["DivisionCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DivisionName", oRow["DivisionName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ITEMGROUP", oRow["ITEMGROUP"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }

                    if (oRow.RowState == DataRowState.Modified)
                    {

                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "UPDATE ITEM_DIVISION SET  DivisionCode = @DivisionCode, DivisionName=@DivisionName, ITEMGROUP=@ITEMGROUP WHERE DivisionCode=@OldDivCode";

                        oCommand.Parameters.Add(new SqlParameter("@OldDivCode", oRow["OldDivCode"]));
                        oCommand.Parameters.Add(new SqlParameter("@DivisionCode", oRow["DivisionCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DivisionName", oRow["DivisionName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ITEMGROUP", oRow["ITEMGROUP"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        public SqlException UpdateItemCategory(DataTable dt)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                foreach (DataRow oRow in dt.Rows)
                {
                    if (oRow.RowState == DataRowState.Added)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO ITEM_CATEGORY (DivCode, CategoryCode, CategoryName) VALUES (@DivCode, @CategoryCode, @CategoryName)";
                        oCommand.Parameters.Add(new SqlParameter("@DivCode", oRow["DivCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CategoryCode", oRow["CategoryCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CategoryName", oRow["CategoryName"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }

                    if (oRow.RowState == DataRowState.Modified)
                    {

                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "UPDATE ITEM_CATEGORY SET  CategoryCode=@CategoryCode, CategoryName=@CategoryName WHERE CategoryCode=@OldCatCode AND DivCode = @DivCode";

                        oCommand.Parameters.Add(new SqlParameter("@OldCatCode", oRow["OldCatCode"]));
                        oCommand.Parameters.Add(new SqlParameter("@DivCode", oRow["DivCode"]));
                        oCommand.Parameters.Add(new SqlParameter("@CategoryCode", oRow["CategoryCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CategoryName", oRow["CategoryName"].ToString()));
                        

                        oCommand.ExecuteNonQuery();
                    }
                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }
        
        public SqlException UpdateItemClass(DataTable dt)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                foreach (DataRow oRow in dt.Rows)
                {
                    if (oRow.RowState == DataRowState.Added)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO ITEM_CLASS (DivCode, CategoryCode, ClssCode, ClssName) VALUES (@DivCode, @CategoryCode, @ClssCode, @ClssName)";
                        oCommand.Parameters.Add(new SqlParameter("@DivCode", oRow["DivCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CategoryCode", oRow["CategoryCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ClssCode", oRow["ClssCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ClssName", oRow["ClssName"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }

                    if (oRow.RowState == DataRowState.Modified)
                    {

                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "UPDATE ITEM_CATEGORY SET  ClssCode=@ClssCode, ClssName=@ClssName WHERE DivCode=@DivCode and" +
                            " CategoryCode=@CategoryCode AND ClssCode = @OldClssCode";

                        oCommand.Parameters.Add(new SqlParameter("@DivCode", oRow["DivCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CategoryCode", oRow["CategoryCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@OldClssCode", oRow["OldClssCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ClssCode", oRow["ClssCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ClssName", oRow["ClssName"].ToString()));


                        oCommand.ExecuteNonQuery();
                    }
                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }
        #endregion
    }
}
